package com.hrp.service;

import com.hrp.domain.TestExportExcel;
import com.hrp.domain.TransportRecord;
import com.hrp.hrpdao.AccountsReceivableDao;
import com.hrp.hrpdao.ExcelExportDao;
import com.hrp.hrpdao.TransportRecordDao;
import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.lang.reflect.Field;
import java.util.*;

/**
 * Created by Administrator on 2019/5/3 0003.
 */
@Service
public class ExcelExportService {

    private static final Logger logger = LoggerFactory.getLogger(TransportRecordService.class);

    @Autowired
    private ExcelExportDao excelExportDao;


    @Autowired
    private AccountsReceivableDao accountsReceivableDao;

    public HSSFWorkbook getTransportRecordByPage(Integer page, Integer rows,String sort, String order,String sendTimeKsrq,String sendTimeJsrq,String varietiesIdStr,String supplierIdStr,String receiverIdStr) {
        Map<String,String> params = new HashMap<>();
        params.put("page",""+page+"");
        params.put("rows",""+rows+"");
        params.put("sort",""+sort+"");
        params.put("order",""+order+"");
        params.put("sendTimeKsrq",""+sendTimeKsrq+"");
        params.put("sendTimeJsrq",""+sendTimeJsrq+"");
        params.put("varietiesIdStr",""+varietiesIdStr+"");
        params.put("supplierIdStr",""+supplierIdStr+"");
        params.put("receiverIdStr",""+receiverIdStr+"");

        HSSFWorkbook hssfWorkbook=null;


        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map
        try{
            //Integer total = transportRecordQueryDao.getTotalCount();
            //List<Jxkhzq> list = jxkhzqDao.getJxkhzqByPage(10,20);

            List<Map<String,Object>>  lists = excelExportDao.getTransportRecordByPageForProc(params);

            hssfWorkbook =getExportWookBook(lists);
            return hssfWorkbook;
        }catch (Exception e) {
            e.printStackTrace();
            logger.info("获取分页账期数据失败");
            return hssfWorkbook;
        }
    }

    public HSSFWorkbook getExportWookBook(List<Map<String,Object>> lists){
        //List<TestExportExcel> queryAll = mapper.queryAll();


        //工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //设置导出的表的名字
        //设置头
        int rowNum =1;


       String [] headers = {"序号","日期","种类id","种类名称","产地id","产地名称"
               ,"收货方id","收货方名称","车牌号","车毛重","车身重","价格","净重","金额","收料人","备注","是否结账"};


        HSSFRow row = sheet.createRow(0);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        HSSFFont font = workbook.createFont();
        font.setBold(true);


        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);

        for(int i=0;i<headers.length;i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.setCellStyle(style);
        }

        //给excel填充数据
      try{
          for (Map<String,Object> transportRecordMap : lists) {
              HSSFRow rown = sheet.createRow(rowNum);
//            Set<String> sets = transportRecordMap.keySet();
//            Iterator<String> keyIterator = sets.iterator();
//            while (keyIterator.hasNext()){
//                String keyName = keyIterator.next();
//                System.out.println(keyName);
//                rown.createCell(cellNum).setCellValue(transportRecordMap.get(keyName));
//                cellNum++;
//            }

              String id = transportRecordMap.get("id").toString();
              String sendTime = "";
              if(transportRecordMap.get("sendTime")!=null){
                  sendTime = transportRecordMap.get("sendTime").toString();
              }
              String varietiesId="";
              if(transportRecordMap.get("varietiesId")!=null){
                  varietiesId = transportRecordMap.get("varietiesId").toString();
              }
              String varietiesName = "";
              if(transportRecordMap.get("varietiesName")!=null){
                  varietiesName = transportRecordMap.get("varietiesName").toString();
              }
              String price = "";
              if( transportRecordMap.get("price")!=null){
                  price = transportRecordMap.get("price").toString();
              }
              String supplierId ="";
              if(transportRecordMap.get("supplierId")!=""){
                  supplierId = transportRecordMap.get("supplierId").toString();
              }
              String supplierName = "";
              if(transportRecordMap.get("supplierName")!=null){
                  supplierName = transportRecordMap.get("supplierName").toString();
              }
              String receiverId = "";
              if(transportRecordMap.get("receiverId")!=null){
                  receiverId = transportRecordMap.get("receiverId").toString();
              }
              String receiverName = "";
              if(transportRecordMap.get("receiverName")!=null){
                  receiverName = transportRecordMap.get("receiverName").toString();
              }
              String cph = "";
              if(transportRecordMap.get("cph")!=null){
                  cph = transportRecordMap.get("cph").toString();
              }
              String cmz = "";
              if(transportRecordMap.get("cmz")!=null){
                  cmz = transportRecordMap.get("cmz").toString();
              }
              String csz = "";
              if(transportRecordMap.get("csz")!=null){
                  csz = transportRecordMap.get("csz").toString();
              }
              Double jz = 0.0;
              if(transportRecordMap.get("jz")!=null){
                  jz = Double.parseDouble(transportRecordMap.get("jz").toString());
              }
              String je = "";
              if(transportRecordMap.get("je")!=null){
                  je = transportRecordMap.get("je").toString();
              }
              String jsr = "";
              if(transportRecordMap.get("jsr")!=null){
                  jsr = transportRecordMap.get("jsr").toString();
              }
              //String times = transportRecordMap.get("times").toString();
              String memo = "";
              if(transportRecordMap.get("memo")!=null){
                  memo = transportRecordMap.get("memo").toString();
              }

              String isSettleAccounts = "";
              if(transportRecordMap.get("isSettleAccounts")!=null){
                  isSettleAccounts = transportRecordMap.get("isSettleAccounts").toString();
              }

              String isSettleAccountsName="未结账";
              if(isSettleAccounts.equals("1")) {
                  isSettleAccountsName = "已结账";
              }


              rown.createCell(0).setCellValue(id);
              rown.createCell(1).setCellValue(sendTime);
              rown.createCell(2).setCellValue(varietiesId);
              rown.createCell(3).setCellValue(varietiesName);
              rown.createCell(4).setCellValue(supplierId);
              rown.createCell(5).setCellValue(supplierName);
              rown.createCell(6).setCellValue(receiverId);
              rown.createCell(7).setCellValue(receiverName);
              rown.createCell(8).setCellValue(cph);
              rown.createCell(9).setCellValue(cmz);
              rown.createCell(10).setCellValue(csz);
              rown.createCell(11).setCellValue(price);
              rown.createCell(12).setCellValue(jz);
              rown.createCell(13).setCellValue(je);
              rown.createCell(14).setCellValue(jsr);
              rown.createCell(15).setCellValue(memo);
              rown.createCell(16).setCellValue(isSettleAccountsName);

              rowNum++;
          }
      }catch (Exception e){
          e.printStackTrace();
      }
        return workbook;
    }

    public HSSFWorkbook getTransportRecordexportSummary(String sort, String order, String sendTimeKsrq, String sendTimeJsrq,String varietiesIdStr, String supplierIdStr, String receiverIdStr) {

        Map<String,String> params = new HashMap<>();
        params.put("sort",""+sort+"");
        params.put("order",""+order+"");
        params.put("sendTimeKsrq",""+sendTimeKsrq+"");
        params.put("sendTimeJsrq",""+sendTimeJsrq+"");
        params.put("varietiesIdStr",""+varietiesIdStr+"");
        params.put("supplierIdStr",""+supplierIdStr+"");
        params.put("receiverIdStr",""+receiverIdStr+"");

        HSSFWorkbook hssfWorkbook=null;


        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map
        try{
            //Integer total = transportRecordQueryDao.getTotalCount();
            //List<Jxkhzq> list = jxkhzqDao.getJxkhzqByPage(10,20);

            List<Map<String,Object>>  lists = excelExportDao.getTransportRecordSummaryForProc(params);

            hssfWorkbook =getTransportRecordSummaryExportWookBook(lists);
            return hssfWorkbook;
        }catch (Exception e) {
            e.printStackTrace();
            logger.info("获取分页账期数据失败");
            return hssfWorkbook;
        }
    }

    public HSSFWorkbook getTransportRecordSummaryExportWookBook(List<Map<String,Object>> lists){
        //List<TestExportExcel> queryAll = mapper.queryAll();


        //工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //设置导出的表的名字
        //设置头
        int rowNum =1;


        String [] headers = {"产地id","产地名称","收货方id","收货方名称","种类id","种类名称","单价","净重","金额","总金额"};


        HSSFRow row = sheet.createRow(0);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        HSSFFont font = workbook.createFont();
        font.setBold(true);


        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);

        for(int i=0;i<headers.length;i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.setCellStyle(style);
        }

        //给excel填充数据
        try{
            for (Map<String,Object> transportRecordMap : lists) {
                HSSFRow rown = sheet.createRow(rowNum);
//            Set<String> sets = transportRecordMap.keySet();
//            Iterator<String> keyIterator = sets.iterator();
//            while (keyIterator.hasNext()){
//                String keyName = keyIterator.next();
//                System.out.println(keyName);
//                rown.createCell(cellNum).setCellValue(transportRecordMap.get(keyName));
//                cellNum++;
//            }



                String varietiesId="";
                if(transportRecordMap.get("varietiesId")!=null){
                    varietiesId = transportRecordMap.get("varietiesId").toString();
                }
                String varietiesName = "";
                if(transportRecordMap.get("varietiesName")!=null){
                    varietiesName = transportRecordMap.get("varietiesName").toString();
                }

                String supplierId ="";
                if(transportRecordMap.get("supplierId")!=""){
                    supplierId = transportRecordMap.get("supplierId").toString();
                }
                String supplierName = "";
                if(transportRecordMap.get("supplierName")!=null){
                    supplierName = transportRecordMap.get("supplierName").toString();
                }
                String receiverId = "";
                if(transportRecordMap.get("receiverId")!=null){
                    receiverId = transportRecordMap.get("receiverId").toString();
                }
                String receiverName = "";
                if(transportRecordMap.get("receiverName")!=null){
                    receiverName = transportRecordMap.get("receiverName").toString();
                }

                Double price = 0.0;
                if(transportRecordMap.get("price")!=null){
                    price = Double.parseDouble(transportRecordMap.get("price").toString());
                }

                Double jz = 0.0;
                if(transportRecordMap.get("jz")!=null){
                    jz = Double.parseDouble(transportRecordMap.get("jz").toString());
                }

                Double je = 0.0;
                if(transportRecordMap.get("je")!=null){
                    je = Double.parseDouble(transportRecordMap.get("je").toString());
                }

                Double zje = 0.0;
                if(transportRecordMap.get("zje")!=null){
                    zje = Double.parseDouble(transportRecordMap.get("zje").toString());
                }


                rown.createCell(0).setCellValue(supplierId);
                rown.createCell(1).setCellValue(supplierName);
                rown.createCell(2).setCellValue(receiverId);
                rown.createCell(3).setCellValue(receiverName);
                rown.createCell(4).setCellValue(varietiesId);
                rown.createCell(5).setCellValue(varietiesName);
                rown.createCell(6).setCellValue(price);
                rown.createCell(7).setCellValue(jz);
                rown.createCell(8).setCellValue(je);
                rown.createCell(9).setCellValue(zje);

                rowNum++;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return workbook;
    }

    public HSSFWorkbook exportAccountsReceivable(Integer page, Integer rows, String startTime, String endTime,String receiverIdStr) {

        Map<String,String> params = new HashMap<>();
        params.put("page",""+page+"");
        params.put("rows",""+rows+"");
        params.put("startTime",""+startTime+"");
        params.put("endTime",""+endTime+"");
        params.put("receiverIdStr",""+receiverIdStr+"");

        HSSFWorkbook hssfWorkbook=null;


        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map
        try{

            List<Map<String,Object>>  lists = accountsReceivableDao.getAccountsReceivableByPage(params);



            List<Map<String,Object>>  listMergeCell = accountsReceivableDao.getdatagridMergeCells(params);

            hssfWorkbook =getAccountsReceivableExportWookBook(lists,listMergeCell);
            return hssfWorkbook;
        }catch (Exception e) {
            e.printStackTrace();
            logger.info("获取分页账期数据失败");
            return hssfWorkbook;
        }

    }



    public HSSFWorkbook getAccountsReceivableExportWookBook(List<Map<String,Object>> lists,List<Map<String,Object>>  listMergeCell){
        //List<TestExportExcel> queryAll = mapper.queryAll();


        //工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //设置导出的表的名字
        //设置头
        int rowNum =1;


        String [] headers = {"收货地id","收货地","发货地id","发货地","种类id","种类","时间段","单价","材料(净重)","材料(已开票净重)","材料(未开票净重)",
                "应收金额","已开票金额","未开票金额","应收总金额","已开票总金额","未开票总金额","乙方打款总金额","欠款","校验单元格是否合并正确"};


        HSSFRow row = sheet.createRow(0);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        HSSFFont font = workbook.createFont();
        font.setBold(true);

        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.JUSTIFY);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);


        //style.setVerticalAlignment(HorizontalAlignment.CENTER);
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        for(int i=0;i<headers.length;i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.setCellStyle(style);
        }

        //给excel填充数据
        try{
            for (Map<String,Object> transportRecordMap : lists) {
                HSSFRow rown = sheet.createRow(rowNum);
//            Set<String> sets = transportRecordMap.keySet();
//            Iterator<String> keyIterator = sets.iterator();
//            while (keyIterator.hasNext()){
//                String keyName = keyIterator.next();
//                System.out.println(keyName);
//                rown.createCell(cellNum).setCellValue(transportRecordMap.get(keyName));
//                cellNum++;
//            }


                String receiverId = "";
                if(transportRecordMap.get("receiverId")!=null){
                    receiverId = transportRecordMap.get("receiverId").toString();
                }
                String receiverName = "";
                if(transportRecordMap.get("receiverName")!=null){
                    receiverName = transportRecordMap.get("receiverName").toString();
                }
                String supplierId ="";
                if(transportRecordMap.get("supplierId")!=""){
                    supplierId = transportRecordMap.get("supplierId").toString();
                }
                String supplierName = "";
                if(transportRecordMap.get("supplierName")!=null){
                    supplierName = transportRecordMap.get("supplierName").toString();
                }
                String varietiesId="";
                if(transportRecordMap.get("varietiesId")!=null){
                    varietiesId = transportRecordMap.get("varietiesId").toString();
                }
                String varietiesName = "";
                if(transportRecordMap.get("varietiesName")!=null){
                    varietiesName = transportRecordMap.get("varietiesName").toString();
                }

                String timeSlot = "";
                if(transportRecordMap.get("timeSlot")!=null){
                    timeSlot = transportRecordMap.get("timeSlot").toString();
                }

                Double price = 0.0;
                if(transportRecordMap.get("price")!=null){
                    price = Double.parseDouble(transportRecordMap.get("price").toString());
                }

                Double jz = 0.0;
                if(transportRecordMap.get("jz")!=null){
                    jz = Double.parseDouble(transportRecordMap.get("jz").toString());
                }

                Double alreadySettleAccountsJz = 0.0;
                if(transportRecordMap.get("alreadySettleAccountsJz")!=null){
                    alreadySettleAccountsJz = Double.parseDouble(transportRecordMap.get("alreadySettleAccountsJz").toString());
                }

                Double withoutSettleAccountsJz = 0.0;
                if(transportRecordMap.get("withoutSettleAccountsJz")!=null){
                    withoutSettleAccountsJz = Double.parseDouble(transportRecordMap.get("withoutSettleAccountsJz").toString());
                }

                Double je = 0.0;
                if(transportRecordMap.get("je")!=null){
                    je = Double.parseDouble(transportRecordMap.get("je").toString());
                }
                Double alreadySettleAccountsMoney = 0.0;
                if(transportRecordMap.get("alreadySettleAccountsMoney")!=null){
                    alreadySettleAccountsMoney = Double.parseDouble(transportRecordMap.get("alreadySettleAccountsMoney").toString());
                }
                Double withoutSettleAccountsMoney = 0.0;
                if(transportRecordMap.get("withoutSettleAccountsMoney")!=null){
                    withoutSettleAccountsMoney = Double.parseDouble(transportRecordMap.get("withoutSettleAccountsMoney").toString());
                }

                Double shouldMoney = 0.0;
                if(transportRecordMap.get("shouldMoney")!=null){
                    shouldMoney = Double.parseDouble(transportRecordMap.get("shouldMoney").toString());
                }

                Double alreadySettleAccountsMoneyAll = 0.0;
                if(transportRecordMap.get("alreadySettleAccountsMoneyAll")!=null){
                    alreadySettleAccountsMoneyAll = Double.parseDouble(transportRecordMap.get("alreadySettleAccountsMoneyAll").toString());
                }

                Double withoutSettleAccountsMoneyAll = 0.0;
                if(transportRecordMap.get("withoutSettleAccountsMoneyAll")!=null){
                    withoutSettleAccountsMoneyAll = Double.parseDouble(transportRecordMap.get("withoutSettleAccountsMoneyAll").toString());
                }

                Double acceptedMoney = 0.0;
                if(transportRecordMap.get("acceptedMoney")!=null){
                    acceptedMoney = Double.parseDouble(transportRecordMap.get("acceptedMoney").toString());
                }

                Double arrears = 0.0;
                if(transportRecordMap.get("arrears")!=null){
                    arrears = Double.parseDouble(transportRecordMap.get("arrears").toString());
                }

                rown.createCell(0).setCellValue(receiverId);//收货地id
                rown.createCell(1).setCellValue(receiverName);//收货地
                rown.createCell(2).setCellValue(supplierId);//发货地id
                rown.createCell(3).setCellValue(supplierName);//发货地
                rown.createCell(4).setCellValue(varietiesId);//种类id
                rown.createCell(5).setCellValue(varietiesName);//种类
                rown.createCell(6).setCellValue(timeSlot);//时间段
                rown.createCell(7).setCellValue(price);//单价
                rown.createCell(8).setCellValue(jz);//材料(净重)
                rown.createCell(9).setCellValue(alreadySettleAccountsJz);//材料(已开票净重)
                rown.createCell(10).setCellValue(withoutSettleAccountsJz);//材料(未开票净重)
                rown.createCell(11).setCellValue(je);//应收金额
                rown.createCell(12).setCellValue(alreadySettleAccountsMoney);//已开票金额
                rown.createCell(13).setCellValue(withoutSettleAccountsMoney);//未开票金额
                rown.createCell(14).setCellValue(shouldMoney);//应收总金额
                rown.createCell(15).setCellValue(alreadySettleAccountsMoneyAll);//已开票总金额
                rown.createCell(16).setCellValue(withoutSettleAccountsMoneyAll);//未开票总金额
                rown.createCell(17).setCellValue(acceptedMoney);//乙方打款总金额
                rown.createCell(18).setCellValue(arrears);//欠款
                rown.createCell(19).setCellValue(shouldMoney);//校验单元格是否合并正确

                rowNum++;
            }
        }catch (Exception e){
            e.printStackTrace();
        }



        //合并单元格
        for(Map<String,Object> map:listMergeCell){
            Integer index = Integer.parseInt(map.get("index").toString());
            Integer rowspan = Integer.parseInt(map.get("rowspan").toString());
            CellRangeAddress cra = new CellRangeAddress(index+1, (index+rowspan), 1, 1);
            CellRangeAddress cra1 = new CellRangeAddress(index+1, (index+rowspan), 14, 14);
            CellRangeAddress cra2 = new CellRangeAddress(index+1, (index+rowspan), 15, 15);
            CellRangeAddress cra3 = new CellRangeAddress(index+1, (index+rowspan), 16, 16);
            CellRangeAddress cra4 = new CellRangeAddress(index+1, (index+rowspan), 17, 17);
            CellRangeAddress cra5 = new CellRangeAddress(index+1, (index+rowspan), 18, 18);
            sheet.addMergedRegion(cra);
            sheet.addMergedRegion(cra1);
            sheet.addMergedRegion(cra2);
            sheet.addMergedRegion(cra3);
            sheet.addMergedRegion(cra4);
            sheet.addMergedRegion(cra5);
        }


        return workbook;
    }

    public HSSFWorkbook excelExportForManySheet(Integer page, Integer rows,String sort, String order,String sendTimeKsrq,String sendTimeJsrq,String varietiesIdStr,String supplierIdStr,String receiverIdStr) {
        Map<String,String> params = new HashMap<>();
        params.put("page",""+page+"");
        params.put("rows",""+rows+"");
        params.put("sort",""+sort+"");
        params.put("order",""+order+"");
        params.put("sendTimeKsrq",""+sendTimeKsrq+"");
        params.put("sendTimeJsrq",""+sendTimeJsrq+"");
        params.put("varietiesIdStr",""+varietiesIdStr+"");
        params.put("supplierIdStr",""+supplierIdStr+"");
        params.put("receiverIdStr",""+receiverIdStr+"");

        HSSFWorkbook hssfWorkbook=null;


        Map<String, Object> jsonMap = new HashMap<String, Object>();//定义map
        try{
            //Integer total = transportRecordQueryDao.getTotalCount();
            //List<Jxkhzq> list = jxkhzqDao.getJxkhzqByPage(10,20);

            List<Map<String,Object>>  lists = excelExportDao.getTransportRecordByPageForProc(params);

            hssfWorkbook =getExportWookBookForManySheet(lists);
            return hssfWorkbook;
        }catch (Exception e) {
            e.printStackTrace();
            logger.info("获取分页账期数据失败");
            return hssfWorkbook;
        }
    }

//    Integer fistSupplierId = 0;
//    Integer firstReceiverId = 0;
//    Integer nextSupplierId = 0;
//    Integer nextReceiverId = 0;
//
//
//    fistSupplierId = Integer.parseInt(lists.get(j).get("supplierId").toString());
//    firstReceiverId = Integer.parseInt(lists.get(j).get("receiverId").toString());
//
//    nextSupplierId = Integer.parseInt(lists.get(j).get("supplierId").toString());
//    nextReceiverId = Integer.parseInt(lists.get(j).get("receiverId").toString());

    public HSSFWorkbook getExportWookBookForManySheet(List<Map<String,Object>> lists) {


        String fistSupplierIdName="";
        String firstReceiverIdName="";
        fistSupplierIdName = lists.get(0).get("supplierName").toString();
        firstReceiverIdName = lists.get(0).get("receiverName").toString();


        //工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(fistSupplierIdName+"-"+firstReceiverIdName);
        //设置导出的表的名字
        //设置头
        int rowNum = 1;
        int rowSheetNum=1;

        //String[] headers = {"序号", "日期", "种类id", "种类名称", "产地id", "产地名称"
        //        , "收货方id", "收货方名称", "车牌号", "车毛重", "车身重", "价格", "净重", "金额", "收料人", "备注", "是否结账"};
        String[] headers = {"序号", "日期",  "种类名称",  "产地名称"
                , "收货方名称", "车牌号", "车毛重", "车身重", "价格", "净重", "金额", "收料人", "备注", "是否结账"};

        HSSFRow row = sheet.createRow(0);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        HSSFFont font = workbook.createFont();
        font.setBold(true);

        style.setAlignment(HorizontalAlignment.CENTER);
        style.setFont(font);

        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.setCellStyle(style);
        }


        String fistSupplierId = "";
        String firstReceiverId = "";
        String nextSupplierId = "";
        String nextReceiverId = "";


        //给excel填充数据
        try {
            for (Map<String, Object> transportRecordMap : lists) {


                HSSFRow rown = sheet.createRow(rowSheetNum);

                String id = transportRecordMap.get("id").toString();
                String sendTime = "";
                if (transportRecordMap.get("sendTime") != null) {
                    sendTime = transportRecordMap.get("sendTime").toString();
                }
                String varietiesId = "";
                if (transportRecordMap.get("varietiesId") != null) {
                    varietiesId = transportRecordMap.get("varietiesId").toString();
                }
                String varietiesName = "";
                if (transportRecordMap.get("varietiesName") != null) {
                    varietiesName = transportRecordMap.get("varietiesName").toString();
                }
                String price = "";
                if (transportRecordMap.get("price") != null) {
                    price = transportRecordMap.get("price").toString();
                }
                String supplierId = "";
                if (transportRecordMap.get("supplierId") != "") {
                    supplierId = transportRecordMap.get("supplierId").toString();
                }
                String supplierName = "";
                if (transportRecordMap.get("supplierName") != null) {
                    supplierName = transportRecordMap.get("supplierName").toString();
                }
                String receiverId = "";
                if (transportRecordMap.get("receiverId") != null) {
                    receiverId = transportRecordMap.get("receiverId").toString();
                }
                String receiverName = "";
                if (transportRecordMap.get("receiverName") != null) {
                    receiverName = transportRecordMap.get("receiverName").toString();
                }
                String cph = "";
                if (transportRecordMap.get("cph") != null) {
                    cph = transportRecordMap.get("cph").toString();
                }
                String cmz = "";
                if (transportRecordMap.get("cmz") != null) {
                    cmz = transportRecordMap.get("cmz").toString();
                }
                String csz = "";
                if (transportRecordMap.get("csz") != null) {
                    csz = transportRecordMap.get("csz").toString();
                }
                Double jz = 0.0;
                if (transportRecordMap.get("jz") != null) {
                    jz = Double.parseDouble(transportRecordMap.get("jz").toString());
                }
                String je = "";
                if (transportRecordMap.get("je") != null) {
                    je = transportRecordMap.get("je").toString();
                }
                String jsr = "";
                if (transportRecordMap.get("jsr") != null) {
                    jsr = transportRecordMap.get("jsr").toString();
                }

                String memo = "";
                if (transportRecordMap.get("memo") != null) {
                    memo = transportRecordMap.get("memo").toString();
                }

                String isSettleAccounts = "";
                if (transportRecordMap.get("isSettleAccounts") != null) {
                    isSettleAccounts = transportRecordMap.get("isSettleAccounts").toString();
                }

                String isSettleAccountsName = "未结账";
                if (isSettleAccounts.equals("1")) {
                    isSettleAccountsName = "已结账";
                }

                rown.createCell(0).setCellValue(id);
                rown.createCell(1).setCellValue(sendTime);
                //rown.createCell(2).setCellValue(varietiesId);
                rown.createCell(2).setCellValue(varietiesName);
                //rown.createCell(4).setCellValue(supplierId);
                rown.createCell(3).setCellValue(supplierName);
                //rown.createCell(6).setCellValue(receiverId);
                rown.createCell(4).setCellValue(receiverName);
                rown.createCell(5).setCellValue(cph);
                rown.createCell(6).setCellValue(cmz);
                rown.createCell(7).setCellValue(csz);
                rown.createCell(8).setCellValue(price);
                rown.createCell(9).setCellValue(jz);
                rown.createCell(10).setCellValue(je);
                rown.createCell(11).setCellValue(jsr);
                rown.createCell(12).setCellValue(memo);
                rown.createCell(13).setCellValue(isSettleAccountsName);



                if(rowNum<lists.size()){
                    fistSupplierId = lists.get(rowNum - 1).get("supplierId").toString();
                    firstReceiverId = lists.get(rowNum - 1).get("receiverId").toString();
                    nextSupplierId = lists.get(rowNum).get("supplierId").toString();
                    nextReceiverId = lists.get(rowNum).get("receiverId").toString();
                    System.out.println("fistSupplierId:"+fistSupplierId+"-"+"firstReceiverId:"+firstReceiverId+"-"+"nextSupplierId:"+nextSupplierId+"-"+"nextReceiverId:"+nextReceiverId);



                    if (!fistSupplierId.equals(nextSupplierId) || !firstReceiverId.equals(nextReceiverId)) {

                        fistSupplierIdName = lists.get(rowNum).get("supplierName").toString();
                        firstReceiverIdName = lists.get(rowNum).get("receiverName").toString();

                        rowSheetNum=0;
                        sheet = workbook.createSheet(fistSupplierIdName+"-" + firstReceiverIdName);
                        HSSFRow rowC = sheet.createRow(0);
                        //设置为居中加粗
                        HSSFCellStyle styleC = workbook.createCellStyle();
                        styleC.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
                        HSSFFont fontC = workbook.createFont();
                        font.setBold(true);

                        styleC.setAlignment(HorizontalAlignment.CENTER);
                        styleC.setFont(fontC);

                        for (int i = 0; i < headers.length; i++) {
                            HSSFCell cell = rowC.createCell(i);
                            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                            cell.setCellValue(text);
                            cell.setCellStyle(styleC);
                        }
                    }
                }




                rowNum++;
                rowSheetNum++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }


}
